Spreadsheets

Excel spreadsheet files can be used to define some or all of the regime module logic, and hybrid regimes are possible. Multiple spreadsheets can be called in the same model, or a single spreadsheet can be called multiple times. Spreadsheets are invoked in module logic using the ExecuteSpreadsheet() function.

Note: Spreadsheet functionality was introduced for the first time in Planning Space 16.5 and it will be improved and extended over the next several releases. The main current limitations are: (1) VBA macros can be present in the Excel file but they cannot be used in calculations; and (2) worse performance (slower execution, greater memory consumption) can be expected compared with native Planning Space models. Spreadsheet size is limited for uploads (with a default limit of 4 MB; the default is 6 MBytes for version 16.5 Update 17 and later), but this is configurable as an application tenant setting by the IPS Administrator ('SpreadsheetSizeLimit', which can be set to any value up to 500 MB).

Spreadsheet preparation

Spreadsheets cannot be edited within the Regime Library workspace, so they must be pre-prepared in an external application.

One (or more) named ranges must be created in the spreadsheet to specify an area (or areas) of data that will be transferred between the spreadsheet and Planning Space. Planning Space looks for named ranges with a specific form: the first cell contains the heading text 'Type' and the following column names contain only allowed values. If there is more than one valid named range detected by Planning Space, you need to select a named range in the spreadsheets management tab for the regime.

Named ranges can be located on any sheet in the workbook. Cell references within the named range area can refer to anywhere else in the workbook. For example, you could reserve one sheet for the named range for data transfer, with calculations being performed on other sheets.

You can use IN type rows in the spreadsheet to specify data from Planning Space to be input to the spreadsheet, and OUT type rows to return data back to Planning Space.

For full details see the topic Spreadsheet syntax.

Upload a spreadsheet

Select the regime editor's Spreadsheets tab, then click the Add button in the ribbon. In the dialog, select the required Excel file, and click Open.

Note: Spreadsheet size is limited for uploads with a default limit of 4 MBytes (the default is 6 MBytes for version 16.5 Update 17 and later), but the limit can be modified by the IPS Administrator (up to a value of 500 MB).

The spreadsheet name will appear in the list of spreadsheets with the information fields filled in. The Name field can be edited by clicking inside it. The Name value is used to refer to the spreadsheet in the ExecuteSpreadsheet() function in the module logic.

If the spreadsheet contains only one named range, this will be automatically selected in the Named Range field. Otherwise you will see an error message "The spreadsheet does not define a range named ''". Click inside the Named Range field and a dropdown selector will appear containing all of the detected named ranges.

If a named range does not appear as you expect you will need to re-check the syntax of the cells in an external application (see Spreadsheet syntax).

You can change the active Named Range at any time by clicking in the field and using the dropdown selector. Thus you can set up one spreadsheet with alternative data transfer behaviors defined by different named ranges.

Spreadsheet groups (linked spreadsheets)

Add missing variables

When you upload a spreadsheet and a Named Range is selected, the named range will be scanned for variables that are not defined within Planning Space. Any such variables will be listed in the Error List area:

You can add a variable to Planning Space by right-clicking an error message, click Add Variable As and select the variable type.

The button Add Missing Variables will add all of the missing variables in one step.

Spreadsheet preview

To open a view window, select a spreadsheet in the list and click the Show Preview button (or use the context menu). The spreadsheet cannot be modified in Planning Space.

The view window can be un-docked and re-sized over the whole display screen by clicking the down arrow at the top of the window and selecting Float.

Save to Disk (export spreadsheet)

A stored spreadsheet can be exported to the local file system in XLSX file format by selecting the spreadsheet in the list and clicking the Save To Disk button (or use the context menu).

Replace a spreadsheet

An uploaded spreadsheet can be replaced by selecting the spreadsheet in the list and clicking the Replace button (or use the context menu).

You can use the Save To Disk and Replace functions together to perform updates and maintenance work on regime spreadsheets.

Using spreadsheets in modules

Uploaded spreadsheets can be used in any regime module, and the same spreadsheet can be invoked multiple times.

See Spreadsheets in modules for guidance.